drop table board_guest;

create table board_guest(
	no number primary key,
	name varchar2(50) not null,
	password varchar2(50),
	content varchar2(200) not null,
	time_posted date not null
)

create sequence board_guest_seq nocache;

insert into board_guest(no, name, password ,content, time_posted) 
values(board_guest_seq.nextval,'미정', '','하이루~', sysdate);

select * from board_guest;



drop table board_spring

create table board_spring(
	no number primary key,
	title varchar2(200) not null,
	content varchar2(4000) not null,
	time_posted date,
	hits number default 0,
	orgfilename varchar2(200),
	savefilename varchar2(200),
	content_type varchar2(50),
	ref number not null,
	restep number default 0,
	relevel number default 0,
	category varchar2(100) not null
) 

create sequence board_spring_no_seq nocache;

create sequence board_reply_no_seq nocache;

create table board_reply (
	re_no number primary key,
	content varchar2(400) not null,
	time_posted date,
	id varchar2(100) constraint fk_member_inven_id references member_inven,
	no number constraint fk_board_spring_no references board_spring
)

drop table member_inven

create table member_inven(
	id varchar2(100) primary key,
	password varchar2(100) not null,
	name varchar2(100) not null,
	phone varchar2(100) not null,
	email varchar2(100) not null,
	member_level varchar2(30) not null,
	reg_time date,
	dropout number default 0,
	orgimage varchar2(100),
	saveimage varchar2(100),
	profile varchar2(4000)
);


insert into member_inven (id, password, name, phone, email, member_level, reg_time, dropout, profile) values 
('test', '1234', '회원', '010-1111-2222', '123@gmail.com', 'member', sysdate, 0, '회원소개 짱짱짱')
insert into member_inven (id, password, name, phone, email, member_level, reg_time, dropout, profile) values 
('admin', '1234', '관리자', '010-1111-2222', '123@gmail.com', 'admin', sysdate, 0, '나는 관리자닷! 음화화~ 짱짱짱')

select * from member_inven

drop table report
create table report (
	report_no number primary key,
	report_content varchar2(500) not null
)

create table report_board_history (
  	r_no number not null,
	s_no number not null,
	report_count number not null,
	constraint uk_report_board_history primary key(r_no,s_no),
	constraint fk_report_report_no foreign key(r_no) references report(report_no),
	constraint fk_report_board_spring_no foreign key(s_no) references board_spring(no)
)



create table report_reply_history (
	r_no number not null,
	reply_no number not null,
	report_count number not null,
	constraint uk_report_board_reply_history primary key(r_no,reply_no),
	constraint fk_report_report_reply_no foreign key(r_no) references report(report_no),
	constraint fk_report_board_reply_no foreign key(reply_no) references board_reply(re_no)
)